package my.mysql.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import my.mysql.dao.IUserDAO;
import my.mysql.vo.User;

public class IUserDAOImpl implements IUserDAO{
	private Connection conn = null;
	
	
	public IUserDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public boolean doCreate(User user) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO user(uid, username, password, score, reg_date) VALUES (?,?,?,?,?)";

		try{
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUid());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, user.getPassword());
			pstmt.setInt(4, user.getScore());
			pstmt.setDate(5, new java.sql.Date(user.getReg_date().getTime()));
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e)	{
			throw e;
		} finally	{
			try	{
				pstmt.close();
			} catch (Exception e)	{
				
			}
		}
		
		return flag;
	}

	@Override
	public boolean doDelete(int uid) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM user WHERE uid=?";
		
		try{
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, uid);
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e)	{
			throw e;
		} finally	{
			try	{
				pstmt.close();
			} catch (Exception e)	{
				
			}
		}
		
		return flag;
	}

	@Override
	public boolean doUpdate(User user) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "UPDATE user SET username=?, password=password(?), score=?, reg_date=?) WHERE uid=?";
		
		try{
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUid());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, user.getPassword());
			pstmt.setInt(4, user.getScore());
			pstmt.setDate(5, new java.sql.Date(user.getReg_date().getTime()));
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e)	{
			throw e;
		} finally	{
			try	{
				pstmt.close();
			} catch (Exception e)	{
				
			}
		}
		
		return flag;
	}

	@Override
	public List findAll(int currentPage, int lineSize, String keyword)
			throws Exception {
		List allUsers = new ArrayList();
		PreparedStatement pstmt = null;
		String sql;
		
		try{
			/*
			//for Oracle statement
			String sql = "SELECT temp.* FROM "
					+ "(SELECT uid, username, password, score, reg_date, ROWNUM rn "
					+ "FROM user "
					+ "WHERE (uid LIKE upper(?) OR username LIKE upper(?) OR score LIKE upper(?) "
					+ "OR to_char(reg_date, 'yyyy') LIKE upper(?) OR to_char(reg_date, 'mm') LIKE upper(?) "
					+ "OR to_char(reg_date, 'dd') LIKE upper(?)) " 	//end of WHERE
					+ "AND ROWNUM <= " + (currPage * pageSize)
					+ ")"		//end of 2nd SELECT
					+ "temp WHERE temp.rn >" + (currPage - 1) * pageSize;
			*/
			//for MySQL statement
			sql = "SET @cnt = 0";
			pstmt = this.conn.prepareStatement(sql);
			pstmt.executeQuery();
			
			sql = "select * from ("
				+ "select (@cnt := @cnt + 1) as rn, uid, username, password, score, reg_date from user "
				+ "where uid like upper(?) or username like upper(?) or score like upper(?) "
				+ ") as temp where "
				+ " temp.rn <= " + (currentPage * lineSize) 
				+ " and temp.rn > " + ((currentPage - 1) * lineSize);
			
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");
			
			ResultSet rs = pstmt.executeQuery();
			
			User user = null;
			int rn = 0;
			while (rs.next()){
				user = new User();
				//rn = rs.getInt(1);
				user.setUid(rs.getInt(2));
				user.setUsername(rs.getString(3));
				user.setPassword(rs.getString(4));
				user.setScore(rs.getInt(5));
				user.setReg_date(rs.getDate(6));
				allUsers.add(user);
			}
			rs.close();
		} catch (Exception e)	{
			throw e;
		} finally	{
			try	{
				pstmt.close();
			} catch (Exception e)	{
			}
		}
		return allUsers;
	}

	@Override
	public User findById(int uid) throws Exception {
		User user = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT uid, username, password, score, reg_date FROM user WHERE uid=?";
		
		try{
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, uid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()){
				user = new User();
				user.setUid(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setScore(rs.getInt(4));
				user.setReg_date(rs.getDate(5));
			}
			rs.close();
		} catch (Exception e)	{
			throw e;
		} finally	{
			try	{
				pstmt.close();
			} catch (Exception e)	{
			}
		}
		return user;
	}

	@Override
	public int getAllCount(String keyword) throws Exception {
		PreparedStatement pstmt = null;
		String sql;
		int count = 0;
		
		try{
			sql = "select COUNT(uid) from user "
				+ "where uid like upper(?) or username like upper(?) or score like upper(?) ";
			
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");
			
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()){
				count = rs.getInt(1);
			}
			rs.close();
		} catch (Exception e)	{
			throw e;
		} finally	{
			try	{
				pstmt.close();
			} catch (Exception e)	{
			}
		}
		return count;
	}

}
